package pms.dao.impl;


import pms.dao.MedicineDao;
import pms.entity.Medicine;
import pms.until.JDBCTools;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

public class MedicineDaoImpl implements MedicineDao {

    private JDBCTools tools=new JDBCTools();


    @Override
    public boolean deleteMedicineNumberEqual0() {
        int i=tools.update("delete from t_medicine where medicine_number=0");
        return i>0;
    }

    @Override
    public List<Medicine> qurryMedicineNumberByName(String name) {
        List<Medicine> list=new ArrayList<>();
        Medicine medicine=null;
        ResultSet rs=tools.query("select sum(medicine_number) from t_medicine where medicine_name=?",name);

        try {
            while (rs.next()){
                medicine=new Medicine();
                medicine.setMedicine_number(rs.getInt("sum(medicine_number)"));
                list.add(medicine);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            tools.clossAll();
        }
        return list;
    }

    @Override
    public boolean qurryMedicineNumberUnderThd() {
        LocalDate localDate=LocalDate.now();
        ResultSet rs=tools.query("select * from t_medicine where medicine_date>? group by medicine_name",localDate);
        try {
            if(rs.next()){
                return true;
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            tools.clossAll();
        }
        return false;
    }


    @Override
    public List<Medicine> qurryAllMedicineNumberUnderThd() {
        LocalDate localDate=LocalDate.now();
        Medicine medicine=null;
        List<Medicine> list=new ArrayList<>();
        ResultSet rs=tools.query("select medicine_name,sum(medicine_number),medicine_thd from t_medicine where medicine_date>? group by medicine_name",localDate);
        try {
            medicine=new Medicine();
            while (rs.next()){
                String name=rs.getString("medicine_name");
                int sum=rs.getInt("sum(medicine_number)");
                int thd=rs.getInt("medicine_thd");
                if(sum<thd){
                    medicine.setMedicine_name(name);
                    medicine.setMedicine_number(sum);
                    medicine.setMedicine_thd(thd);
                    list.add(medicine);
                }
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            tools.clossAll();
        }
        return list;
    }

    @Override
    public List qurryAllMedicineDateBeforeNow() {
        Medicine medicine=null;
        List list=new ArrayList<>();
        LocalDate localDate=LocalDate.now();
        ResultSet rs=tools.query("select medicine_name,medicine_date from t_medicine where medicine_date<?",localDate);
        try {
            while (rs.next()){
                medicine=new Medicine();
                LocalDate date=rs.getDate("medicine_date").toLocalDate();
                String name=rs.getString("medicine_name");
                medicine.setMedicine_name(name);
                medicine.setMedicine_date(date);
                list.add(medicine);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            tools.clossAll();
        }
        return list;
    }

    @Override
    public boolean deleteMedicileDateBeforeNow() {
        LocalDate localDate=LocalDate.now();
        int i= tools.update("delete from t_medicine where medicine_date < ?",localDate);
        return i>0;
    }

    @Override
    public boolean qurryMedicineDateBeforeNow() {
        LocalDate localDate=LocalDate.now();
        ResultSet rs=tools.query("select * from t_medicine where medicine_date<?",localDate);
        try {
            return rs.next();
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            tools.clossAll();
        }
        return false;
    }

    @Override
    public List<Medicine> qurryAllNearTermMedicine() {
        Medicine medicine=null;
        LocalDate localDate=LocalDate.now();
        LocalDate oneweek=localDate.plusDays(7);
        List<Medicine> list=new ArrayList<>();
        ResultSet rs=tools.query("select medicine_name,medicine_date,medicine_number from t_medicine");
        try {
            while (rs.next()){
                medicine=new Medicine();
                LocalDate date=rs.getDate("medicine_date").toLocalDate();
                String name=rs.getString("medicine_name");
                int number=rs.getInt("medicine_number");
                if(date.isAfter(localDate)&&date.isBefore(oneweek)){
                    medicine.setMedicine_name(name);
                    medicine.setMedicine_number(number);
                    medicine.setMedicine_date(date);
                    list.add(medicine);
                }
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            tools.clossAll();
        }
        return list;
    }

    @Override
    public boolean qurryNearTermMedicine() {
        LocalDate now=LocalDate.now();
        LocalDate oneweek=now.plusDays(7);
        ResultSet rs=tools.query("select * from t_medicine where medicine_date>? and medicine_date<?",now,oneweek);
        try {
            return rs.next();
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            tools.clossAll();
        }
        return false;
    }

    @Override
    public boolean addMedicine(Medicine medicine) {
        int i=tools.update("insert into t_medicine (medicine_name,medicine_price,medicine_date,medicine_number,medicine_thd) value(?,?,?,?,?)",medicine.getMedicine_name(),medicine.getMedicine_price(),medicine.getMedicine_date(),medicine.getMedicine_number(),medicine.getMedicine_thd());
        return i>0;
    }

    @Override
    public boolean updateMedicineNumberByNameAndDate(String name, LocalDate date, int number) {
        int i=tools.update("update t_medicine set medicine_number=? where medicine_date=? and medicine_name=?",number,date,name);
        return i>0;
    }

    @Override
    public boolean updateMedicinePriceByName(String name, double price) {
        int i=tools.update("update t_medicine set medicine_price=? where medicine_name=? ",price,name);
        return i>0;
    }

    @Override
    public Medicine queryNameById(Integer id) {
        Medicine medicine=null;
        ResultSet rs=tools.query("select medicine_name from t_medicine where medicine_id=?",id);

        try {
            if (rs.next()){
                medicine=new Medicine();
                medicine.setMedicine_name(rs.getString("medicine_name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            tools.clossAll();
        }
        return medicine;
    }
    @Override
    public Medicine qurryMedicineNumberByID(Integer medicineid) {
        ResultSet rs= tools.query("select medicine_number from t_medicine where medicine_id=?",medicineid);
        Medicine medicine=null;

        try {
            if (rs.next()){
                medicine=new Medicine();
                medicine.setMedicine_number(rs.getInt("medicine_number"));
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            tools.clossAll();
        }
        return medicine;
    }

    @Override
    public List<Medicine> qurryMedicineByThd() {
        List<Medicine> list=new ArrayList<>();

        ResultSet rs=tools.query("select * from t_medicine where medicine_thd>medicine_number");

        Medicine medicine=null;
        try {
            while (rs.next()){
                medicine=new Medicine();
                medicine.setMedicine_id(rs.getInt("medicine_id"));
                medicine.setMedicine_name(rs.getString("medicine_name"));
                medicine.setMedicine_price(rs.getDouble("medicine_price"));
                medicine.setMedicine_number(rs.getInt("medicine_number"));
                list.add(medicine);
            }

        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            tools.clossAll();
        }
        return list;
    }

    @Override
    public Medicine qurryMedicineByID(Integer medicineid) {
        ResultSet rs= tools.query("select * from t_medicine where medicine_id=?",medicineid);
        Medicine medicine=null;

        try {
            if (rs.next()){
                medicine=new Medicine();
                medicine.setMedicine_number(rs.getInt("medicine_number"));
                medicine.setMedicine_name(rs.getString("medicine_name"));
                medicine.setMedicine_date(rs.getDate("medicine_date").toLocalDate());
                medicine.setMedicine_id(rs.getInt("medicine_id"));
                medicine.setMedicine_price(rs.getDouble("medicine_price"));

            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            tools.clossAll();
        }
        return medicine;

    }

    @Override
    public List<Medicine> queryMedicineByMedicinenameForLeave(String medicinename) {
        List<Medicine> list=new ArrayList<>();
        ResultSet rs= tools.query("select * from t_medicine where medicine_name=?",medicinename);
        Medicine medicine=null;

        try {
            if (rs.next()){
                medicine=new Medicine();
                medicine.setMedicine_number(rs.getInt("medicine_number"));
                medicine.setMedicine_name(rs.getString("medicine_name"));
                medicine.setMedicine_date(rs.getDate("medicine_date").toLocalDate());
                medicine.setMedicine_id(rs.getInt("medicine_id"));
                medicine.setMedicine_price(rs.getDouble("medicine_price"));

                list.add(medicine);

            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            tools.clossAll();
        }
        return list;
    }

    @Override
    public boolean updateMedicineNumberForUp(int medicineid,int medicinenumber) {
        int i= tools.update("update t_medicine set medicine_number=medicine_number+? where medicine_id=? ",medicinenumber,medicineid);
        return i>0;

    }

    @Override
    public boolean updateMedicineNumberForDown(int medicineid,int medicinenumber) {
        int i= tools.update("update t_medicine set medicine_number=medicine_number-? where medicine_id=? ",medicinenumber,medicineid);
        return i>0;
    }

}
